package database;


import gui.ExceptionEvent;

import java.sql.*;

public class AlbumData extends DataSuperclass
{
    public AlbumData()
    {
        con = OracleConnection.getInstance().getConnection();
    }
    public boolean insertSinger(Integer upc, String sname )
    {
        try
        {
            ps = con.prepareStatement("INSERT INTO lead_singer VALUES (?,?)");
            
            ps.setInt(1, upc.intValue());
            ps.setString(2, sname);
            
            ps.executeUpdate();
            con.commit();
            return true;
        }
        catch (SQLException ex)
        {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            try
            {
                con.rollback();
                return false;
            }
            catch (SQLException ex2)
            {
                event = new ExceptionEvent(this, ex2.getMessage());
                fireExceptionGenerated(event);
                return false;
            }
        }
    }
    
    public ResultSet editSingers()
    {
        try
        {
            ps = con.prepareStatement("SELECT s.singer_name FROM lead_singer s ORDER BY LOWER(s.singer_name)", // LOWER is to sort alphabetical otherwise sql does uppercase A-Z then lowercase a-z
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE); 
            ResultSet rs = ps.executeQuery();
            return rs;
        }
        catch (SQLException ex)
        {

            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            return null;
        }
    }
    
	public ResultSet showSingers() {
		try
        {
            ps = con.prepareStatement("SELECT s.upc, i.title, s.singer_name FROM lead_singer s join item i on s.upc=i.upc ORDER BY LOWER(i.title)",
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE); // you can't edit once you join another table!?!
            ResultSet rs = ps.executeQuery();
            return rs;
        }
        catch (SQLException ex)
        {

            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            return null;
        }
	}
	
	public ResultSet showSongs() {
		try
        {
            ps = con.prepareStatement("SELECT s.upc, i.title, s.song_title FROM has_song s join item i on s.upc=i.upc ORDER BY LOWER(i.title)",
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE); // you can't edit once you join another table!?!
            ResultSet rs = ps.executeQuery();
            return rs;
        }
        catch (SQLException ex)
        {

            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            return null;
        }
	}

    public boolean findSingerAndUPC(int upc, String sname) {

		try
        {
            ps = con.prepareStatement("SELECT upc FROM lead_singer where upc = ? AND singer_name = ?");
            ps.setInt(1, upc);
            ps.setString(2, sname);
            ResultSet rs = ps.executeQuery();
            if (rs.next())
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            return false;
        }

	}
    
    public boolean insertSong(Integer upc, String stitle )
    {
        try
        {
            ps = con.prepareStatement("INSERT INTO has_song VALUES (?,?)");
            
            ps.setInt(1, upc.intValue());
            ps.setString(2, stitle);
            
            ps.executeUpdate();
            con.commit();
            return true;
        }
        catch (SQLException ex)
        {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            try
            {
                con.rollback();
                return false;
            }
            catch (SQLException ex2)
            {
                event = new ExceptionEvent(this, ex2.getMessage());
                fireExceptionGenerated(event);
                return false;
            }
        }
    }
    
    public ResultSet editSongs()
    {
        try
        {
            ps = con.prepareStatement("SELECT song_title FROM has_song",
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE); // you can't edit once you join another table!?!
            ResultSet rs = ps.executeQuery();
            return rs;
        }
        catch (SQLException ex)
        {

            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            return null;
        }
    }

    public boolean findSongAndUPC(int upc, String stitle) {

		try
        {
            ps = con.prepareStatement("SELECT upc FROM has_song where upc = ? AND song_title = ?");
            ps.setInt(1, upc);
            ps.setString(2, stitle);
            ResultSet rs = ps.executeQuery();
            if (rs.next())
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (SQLException ex)
        {
            ExceptionEvent event = new ExceptionEvent(this, ex.getMessage());
            fireExceptionGenerated(event);
            return false;
        }

	}

}
